'use strict';

const Controller = require('egg').Controller;

class BlogController extends Controller {
  async index() {
    this.ctx.body = '这是api';
  }

  // 所有数据
  async allList() {
    const sql = 'SELECT ' +
      'article.id as id,' +
      'article.title as title,' +
      'type.typeName as type,' +
      'article.introduce as introduce,' +
      'article.content as content,' +
      'FROM_UNIXTIME(article.addTime,"%Y-%m-%d %H:%i:%s" ) as addTime,' +
      'article.view_count as view_count ' +
      'FROM article LEFT JOIN type ON article.type_id = type.id ' +
      'WHERE status=1';
    const res = await this.app.mysql.query(sql);
    this.ctx.body = {
      list: res,
    };
  }

  /**
   * 1. 获取文章详情
   * 2. 记录获取次数等价于访问量
   */
  async detail() {
    const id = this.ctx.params.id;
    const sql = 'SELECT ' +
      'article.id as id,' +
      'type.id as typeId,' +
      'article.title as title,' +
      'type.typeName as type,' +
      'article.introduce as introduce,' +
      'article.content as content,' +
      'FROM_UNIXTIME(article.addTime,"%Y-%m-%d %H:%i:%s" ) as addTime,' +
      'article.view_count as view_count ' +
      'FROM article LEFT JOIN type ON article.type_id = type.id ' +
      'WHERE article.id = ' + id;
    const res = await this.app.mysql.query(sql);
    const sql_plus = 'UPDATE article SET view_count=view_count+1 WHERE id=' + id;
    this.app.mysql.query(sql_plus);
    this.ctx.body = {
      data: res[0],
    };
  }

  // 获取种类列表
  async typeList() {
    const sql = 'SELECT type.id AS id,typeName,orderNum,COUNT(article.type_id) AS total ' +
      'FROM article LEFT JOIN type ON article.type_id = type.id ' +
      'GROUP BY type.id ' +
      'ORDER BY orderNum';
    const res = await this.app.mysql.query(sql);
    this.ctx.body = {
      list: res,
    };
  }

  // 获取列表通过类别
  async listByType() {
    const type_id = this.ctx.params.type_id;
    const sql_pub = 'SELECT ' +
      'article.id as id,' +
      'article.title as title,' +
      'type.typeName as type,' +
      'article.introduce as introduce,' +
      'article.content as content,' +
      'FROM_UNIXTIME(article.addTime,"%Y-%m-%d %H:%i:%s" ) as addTime,' +
      'article.view_count as view_count ' +
      'FROM article LEFT JOIN type ON article.type_id = type.id';

    let sql = sql_pub + ' ORDER BY view_count DESC limit 10';
    if ((type_id / 1) > 0) {
      sql = sql_pub + ' WHERE article.status = 1 AND type_id=' + type_id;
    } else {
      sql = sql_pub + ' WHERE article.status = 1';
    }
    const res = await this.app.mysql.query(sql);
    this.ctx.body = {
      list: res,
    };
  }

  // 获取访问次数
  async getVisitor() {
    const sql = 'SELECT COUNT(id) AS allRow,SUM(times) AS allTimes FROM visit';
    const res = await this.app.mysql.query(sql);
    this.ctx.body = {
      data: res[0],
    };
  }

}

module.exports = BlogController;
